#-*- encoding: utf-8 -*-
import win32com.client 
from Misc.Utils import *
import numpy as np
from Common.Calendar import *

from DataAccess.QueryBlp import *
from DataAccess.DBConnFactory import DBConnFactory

from string import Template
from datetime import date,timedelta

from ReportTool.HldReport import *
from ReportTool.PnLReport import *
from ReportTool.NAVReport import *
from ReportTool.MktValReport import *
from ReportTool.TradeReport import *
from ReportTool.RealizedPnL import *
from ReportTool.OnshoreHldReport import *

from Config.ReportConfig import *

def CPPIB_MTD_Report(ref_date):

	tplt_file = TPLT_PATH + 'Data Collection for CPPIB.xls'
	xApp = win32com.client.Dispatch("Excel.Application")
	xBook = xApp.Workbooks.Open(tplt_file)
	bm_ticker = '816000'
	
	#Report CPPIB Return
	xSht = xBook.Worksheets('Report')
	entity = 'SC032_F'
	
	d_pre_1m = end_of_mth(ref_date, -1) if end_of_mth(ref_date, -1)> date(2013,7,10) else date(2013,7,10)
	d_last_year = end_of_mth(ref_date, -ref_date.month) if end_of_mth(ref_date, -ref_date.month)>date(2013,7,10) else date(2013,7,10)
	
	nvpu_crt = query_latest_NAV(ref_date, [entity])[0][2]
	nvpu_pre_1m = query_latest_NAV(d_pre_1m, [entity])[0][2]
	nvpu_last_year = query_latest_NAV(d_last_year, [entity])[0][2]
	bm_close_crt = query_latest_index_price(ref_date, bm_ticker)
	bm_close_pre_1m = query_latest_index_price(d_pre_1m, bm_ticker)
	bm_close_last_year = query_latest_index_price(d_last_year, bm_ticker)
	
	xSht.Cells(10, 4).Value = nvpu_crt/nvpu_pre_1m - 1
	xSht.Cells(11, 4).Value = nvpu_crt/nvpu_last_year - 1
	xSht.Cells(10, 5).Value = bm_close_crt/bm_close_pre_1m - 1
	xSht.Cells(11, 5).Value = bm_close_crt/bm_close_last_year - 1
	
	#Report CPPIB Portfolio
	xSht = xBook.Worksheets('NT9S SC CPP')
	portf_id = 'SC032'
	
	hld = query_hld(ref_date, [portf_id])
	#pnl = query_pnl(date(ref_date.year, ref_date.month, 1), ref_date, [portf_id])
	pnl = query_pnl(date(2013, 7, 1), ref_date, [portf_id])
	pref_av = query_pref_AV(ref_date, [portf_id])[0][1]
		
	xSht.Cells(8, 4).Value = ref_date
	xSht.Cells(5, 7).Value = pref_av/1000000
		
	expo = {} # expo[(ticker, l/s)] = mkt_exposure
	weight = {}
	for elem in hld:
		expo[(elem[0], elem[3])] = elem[12]
		weight[elem[0]] = elem[12]
		
	blp_tick = []
	blp_eq_tick = []
	rcd = {}

	for elem in pnl:	
		ticker = elem[0]
		sec_type = elem[2]
		long_short = elem[3]
		ret = elem[6]
		trd_curncy = elem[7]
		base_curncy = elem[8]
		exp = expo[(ticker, long_short)] if expo.has_key((ticker, long_short)) else 0
		last_price = query_latest_price(ref_date, ticker)
			
		#blp ticker
		blp_ticker = ticker
		if (sec_type == 'PNOTE') or (sec_type == 'STOCK' and trd_curncy == 'CNY'):
			blp_ticker = ch_ticker_to_blp_ticker(ticker)
		if (sec_type == 'STOCK' and trd_curncy == 'HKD'):
			blp_ticker = hk_ticker_to_blp_ticker(ticker)
			
		region = sec_region(blp_ticker)
			
		blp_tick.append(blp_ticker)
		if is_equity(blp_ticker):
			blp_eq_tick.append(blp_ticker)
				
		rcd[blp_ticker] = [long_short, exp, ret, last_price, base_curncy, region]
		
	sec_name_dict = query_blp_static(blp_tick, ['NAME'])
	sec_curncy_dict = query_blp_static(blp_tick, ['CRNCY'])
	sec_sector_dict = query_blp_static(blp_eq_tick, ['ICB_SUPERSECTOR_NUM', 'GICS_SECTOR_NAME', 'GICS_SUB_INDUSTRY_NAME', 'GICS_INDUSTRY_GROUP', 'GICS_INDUSTRY_GROUP_NAME'])
	mkt_cap_dict = query_blp_latest(blp_eq_tick, ['CUR_MKT_CAP'], ref_date)
	volume_avg_3m_dict = query_blp_average(blp_eq_tick, ['PX_VOLUME'], ref_date - timedelta(days=90), ref_date)
		
	row = 5
	for tick in rcd.keys():
		long_short = rcd[tick][0]
		exp = rcd[tick][1] 
		ret = rcd[tick][2]
		last_price = rcd[tick][3] 
		base_curncy = rcd[tick][4] 
		region = rcd[tick][5]
		sec_name = sec_name_dict[tick]['NAME']
		local_curncy = sec_curncy_dict[tick]['CRNCY']
		fx = query_latest_fx(ref_date, base_curncy, local_curncy)
		icb_num = sec_sector_dict[tick]['ICB_SUPERSECTOR_NUM'] if sec_sector_dict.has_key(tick) else 'Others'
		gics_name = sec_sector_dict[tick]['GICS_SECTOR_NAME'] if sec_sector_dict.has_key(tick) else 'Others'
		gics_sub = sec_sector_dict[tick]['GICS_SUB_INDUSTRY_NAME'] if sec_sector_dict.has_key(tick) else 'Others'
		gics_industry_group = sec_sector_dict[tick]['GICS_INDUSTRY_GROUP'] if sec_sector_dict.has_key(tick) else 'Others'
		gics_industry_group_name = sec_sector_dict[tick]['GICS_INDUSTRY_GROUP_NAME'] if sec_sector_dict.has_key(tick) else 'Others'
					
		if mkt_cap_dict.has_key(tick):
			mktcap_local = mkt_cap_dict[tick]['CUR_MKT_CAP']
			mktcap_base = mktcap_local / fx
		else:
			mktcap_local = 'Others'
			mktcap_base = 'Others'
				
		if volume_avg_3m_dict.has_key(tick):
			volume_avg_3m = volume_avg_3m_dict[tick]['PX_VOLUME']
			xSht.Cells(row, 26).Formula = '=Y' + str(row) + '*V' + str(row) + '/X' + str(row)
			xSht.Cells(row, 27).Formula = '=' + str(abs(pref_av*exp)) + '/(Z' + str(row) + '*0.3)'
		else:
			volume_avg_3m = 'Others'
			xSht.Cells(row, 26).Value = 'Others'
			xSht.Cells(row, 27).Value = 'Others'
	
		xSht.Range(xSht.Cells(row, 12), xSht.Cells(row, 25)).Value = (tick, region, exp, exp, ret, long_short,
		sec_name, icb_num, mktcap_local, mktcap_base, last_price, local_curncy, fx, volume_avg_3m)
			
		xSht.Cells(row, 28).Value = gics_name
		xSht.Cells(row, 29).Value = gics_sub
		xSht.Cells(row, 30).Value = gics_industry_group_name
		xSht.Cells(row, 31).Value = gics_industry_group		
			
		row += 1
	
	#VaR & Volatility
	r = query_NAV_series(ref_date - timedelta(days=365), ref_date, [entity], is_per_unit=True, is_official=False)
	nav_dict = {}
	if r:
		for elem in r:
			if not TradingCalendar().is_holiday(elem[1].date()):
				nav_dict[elem[1]] = elem[2]
	
	xSht.Cells(8, 10).Value = calc_volatility(nav_dict)
	xSht.Cells(9, 10).Value = calc_var(ref_date, weight)

	#Report Benchmark
	xSht = xBook.Worksheets('S&P CITIC 300')
	
	weight = {}
	bm_weight = query_latest_index_weight(ref_date, bm_ticker)
	for elem in bm_weight:
		weight[elem[0]] = elem[4]
	
	blp_tick = []
	rcd = {}
	bm_pnl = query_benchmark_pnl(date(ref_date.year, ref_date.month, 1), ref_date, bm_ticker)
	#bm_pnl = query_benchmark_pnl(date(ref_date.year, 9, 1), ref_date, bm_ticker)
	
	for sec_tick in bm_pnl.keys():
		blp_ticker = ch_ticker_to_blp_ticker(sec_tick)
		region = sec_region(blp_ticker)
		sec_exp = weight[sec_tick] if weight.has_key(sec_tick) else 0
		sec_ret = bm_pnl[sec_tick]
		
		rcd[blp_ticker] = [region, sec_exp, sec_ret]
		blp_tick.append(blp_ticker)

	sec_name_dict = query_blp_static(blp_tick, ['NAME'])
	sec_sector_dict = query_blp_static(blp_tick, ['ICB_SUPERSECTOR_NUM', 'GICS_SECTOR_NAME', 'GICS_SUB_INDUSTRY_NAME', 'GICS_INDUSTRY_GROUP', 'GICS_INDUSTRY_GROUP_NAME'])
	last_price_dict = query_blp_latest(blp_tick, ['PX_LAST'], ref_date)
	mkt_cap_dict = query_blp_latest(blp_tick, ['CUR_MKT_CAP'], ref_date)
	volume_avg_3m_dict = query_blp_average(blp_tick, ['PX_VOLUME'], ref_date - timedelta(days=90), ref_date)
	
	row = 5
	for tick in rcd.keys():
		long_short = 'LONG'
		region = rcd[tick][0]
		exp = rcd[tick][1]
		ret = rcd[tick][2]
		last_price = last_price_dict[tick]['PX_LAST']
		sec_name = sec_name_dict[tick]['NAME']
		icb_num = sec_sector_dict[tick]['ICB_SUPERSECTOR_NUM'] if sec_sector_dict.has_key(tick) else 'Others'
		gics_name = sec_sector_dict[tick]['GICS_SECTOR_NAME'] if sec_sector_dict.has_key(tick) else 'Others'
		gics_sub = sec_sector_dict[tick]['GICS_SUB_INDUSTRY_NAME'] if sec_sector_dict.has_key(tick) else 'Others'
		gics_industry_group = sec_sector_dict[tick]['GICS_INDUSTRY_GROUP'] if sec_sector_dict.has_key(tick) else 'Others'
		gics_industry_group_name = sec_sector_dict[tick]['GICS_INDUSTRY_GROUP_NAME'] if sec_sector_dict.has_key(tick) else 'Others'
		mktcap_local = mkt_cap_dict[tick]['CUR_MKT_CAP'] if mkt_cap_dict.has_key(tick) else 'Others'
		mktcap_base = mktcap_local
		local_curncy = 'CNY'
		fx = 1
		if volume_avg_3m_dict.has_key(tick):
			volume_avg_3m = volume_avg_3m_dict[tick]['PX_VOLUME']
			xSht.Cells(row, 26).Formula = '=Y' + str(row) + '*V' + str(row) + '/X' + str(row)
			xSht.Cells(row, 27).Formula = '=' + str(abs(pref_av*exp)) + '/(Z' + str(row) + '*0.3)'
		else:
			volume_avg_3m = 'Others'
			xSht.Cells(row, 26).Value = 'Others'
			xSht.Cells(row, 27).Value = 'Others'
	
		xSht.Range(xSht.Cells(row, 12), xSht.Cells(row, 25)).Value = (tick, region, exp, exp, ret, long_short,
		sec_name, icb_num, mktcap_local, mktcap_base, last_price, local_curncy, fx, volume_avg_3m)
			
		xSht.Cells(row, 28).Value = gics_name
		xSht.Cells(row, 29).Value = gics_sub
		xSht.Cells(row, 30).Value = gics_industry_group_name
		xSht.Cells(row, 31).Value = gics_industry_group			
		
		row += 1

	#VaR & Volatility
	start_date = date(2013,7,10) if ref_date - timedelta(days=365) < date(2013,7,10) else ref_date - timedelta(days=365)
	r = query_index_price(start_date, ref_date, bm_ticker)
	idx_close_dict = {}
	if r:
		for elem in r:
			idx_close_dict[elem[0]] = elem[1]
			
	xSht.Cells(8, 10).Value = calc_volatility(idx_close_dict)
	xSht.Cells(9, 10).Value = calc_var(ref_date, weight)
	
	rept_file = REPT_PATH + 'Data Collection for CPPIB_' + ref_date.strftime("%Y%m%d") + '.xls'
	xBook.SaveAs(rept_file)
	xBook.Close()
		
	print 'info, CPPIB MTD report completed at ', ref_date.isoformat()
	del xApp

def query_index_weight(ref_date, idx_ticker):

	sql_tpl = Template('''select sec_ticker,
								 pre_adj_close,
								 close,
								 pre_adj_weight,
								 weight
						  from springs_index_weight
						  where ref_date = to_date('${DATE}', 'yyyy-mm-dd')
						  and index_ticker = '${IDX_TICK}' ''')
	
	sql_text = sql_tpl.substitute(DATE = ref_date.isoformat(), IDX_TICK = idx_ticker)
	
	cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()						
	cursor.execute(sql_text)
	return cursor.fetchall()
	
def query_latest_index_weight(ref_date, idx_ticker):

	sql_tpl = Template('''select sec_ticker,
								 pre_adj_close,
								 close,
								 pre_adj_weight,
								 weight
						  from springs_index_weight,
							  (select max(ref_date) as ldate
								from springs_index_weight
								where ref_date <= to_date('${DATE}', 'yyyy-mm-dd'))
						  where ref_date = ldate
						  and index_ticker = '${IDX_TICK}' ''')
	
	sql_text = sql_tpl.substitute(DATE = ref_date.isoformat(), IDX_TICK = idx_ticker)
	
	cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()						
	cursor.execute(sql_text)
	return cursor.fetchall()

def query_benchmark_pnl(start_date, end_date, idx_ticker):

	cum_factor = 1
	d = start_date
	contr = {}
	while d <= end_date:
	
		bm = query_index_weight(d, idx_ticker)
		
		if bm:
			portf_contr = 0
			for elem in bm:
				sec_tick = elem[0]
				pre_adj_close = elem[1]
				close = elem[2]
				pre_adj_weight = elem[3]
				
				sec_contr = pre_adj_weight * (close/pre_adj_close - 1.0)
				portf_contr += sec_contr
				
				if contr.has_key(sec_tick):
					contr[sec_tick] += cum_factor * sec_contr
				else:
					contr[sec_tick] = cum_factor * sec_contr
				
			cum_factor *= (1 + portf_contr)
		
		d += timedelta(days = 1)

	return 	contr
	
def calc_var(ref_date, weight_dict, sample_days = 365, pctg = 95, idx = '816000'):

	sql_tpl = Template('''select crt.Symbol,crt.close/ystd.close - 1
							from
								(select Symbol,PRICE2 as close
								  from DERC_EQACHQUOTE_2
								  where TDate = TO_DATE('${DATE}','yyyymmdd')) crt,
								(select Symbol,PRICE2 as close
								  from DERC_EQACHQUOTE_2,
								(select max(TDate) as ld
								  from DERC_EQACHQUOTE_2
								  where TDate < TO_DATE('${DATE}','yyyymmdd'))
								where TDate = ld) ystd
							where crt.Symbol = ystd.Symbol''')

	sql_tpl_idx = Template('''select TCLOSE/LCLOSE - 1 as ret
							  from CIHDQUOTE
							  where TDate = '${DATE}'
							  and SYMBOL = '${IDX_TICK}' ''')						  
	portf_ret = []
	for i in range(sample_days):
	
		d = ref_date - timedelta(days=i)
		
		if not TradingCalendar().is_holiday(d):
		
			sql_text = sql_tpl_idx.substitute(DATE = d.strftime("%Y%m%d"), IDX_TICK = idx)
			cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()						
			cursor.execute(sql_text)
			idx_ret = cursor.fetchall()[0][0]
			
			ret = 0
			ret_dict = {}
			sql_text = sql_tpl.substitute(DATE = d.strftime("%Y%m%d"))
			cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()	
			cursor.execute(sql_text)
			r = cursor.fetchall()
				
			for elem in r:
				ret_dict[elem[0]] = elem[1]
				
			for tick in weight_dict.keys():
				if ret_dict.has_key(tick):
					ret += weight_dict[tick] * ret_dict[tick]
				else:
					ret += weight_dict[tick] * idx_ret
						
			portf_ret.append(ret)
		
	portf_ret.sort()
	
	return portf_ret[int(len(portf_ret)*(1-pctg/100.0))]	
	
def calc_volatility(daily_price_dict):

	if daily_price_dict:
		price_list = []
		for d, p in sorted(daily_price_dict.items(), key = lambda x:x[0]):
			price_list.append(p)
			
		ret_list = []
		for i in range(len(price_list)-1):
			ret_list.append(price_list[i+1]/price_list[i]-1)
		
		return np.std(ret_list)*np.sqrt(252)

def query_index_price(start_date, end_date, ticker):

	sql_tpl = Template('''select TO_DATE(tdate, 'yyyymmdd'), tclose from CIHDQUOTE
						where symbol = '${TICK}'
							and tdate >= '${START}'
							and tdate <= '${END}'
							order by tdate ''')
			
	sql_text = sql_tpl.substitute(START = start_date.strftime('%Y%m%d'),
									END = end_date.strftime('%Y%m%d'),
									TICK = ticker)
	
	cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()
	cursor.execute(sql_text)
	return cursor.fetchall()